﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.Data;

namespace AcctruePMS.Bll.Common
{
    public class ExcelHelper
    {
        public static HSSFWorkbook hssfWorkBook;

        public static MemoryStream GetExcelWork(DataTable data, string path)
        {

            try
            {
                InitializeWorkBook(path);
                HSSFSheet sheet1 = hssfWorkBook.GetSheet("Sheet1");
                if (sheet1 == null)
                {
                    return null;
                }
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    //for (int j = 0; j < data.Columns.Count; j++)
                    //{
                    //    sheet1.CreateRow(i + 2).CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    //}
                    sheet1.CreateRow(i + 2).CreateCell(0).SetCellValue(i+1);
                    sheet1.CreateRow(i + 2).CreateCell(1).SetCellValue(Convert.ToDateTime(data.Rows[i]["startDate"]).ToString("MM月dd日") + "-" +Convert.ToDateTime(data.Rows[i]["endDate"]).ToString("MM月dd日"));
                    sheet1.CreateRow(i + 2).CreateCell(2).SetCellValue(data.Rows[i]["departWBS"].ToString());
                    sheet1.CreateRow(i + 2).CreateCell(3).SetCellValue(data.Rows[i]["userNo"].ToString());
                    sheet1.CreateRow(i + 2).CreateCell(4).SetCellValue(data.Rows[i]["reportStatusText"].ToString());
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    hssfWorkBook.Write(ms);
                    ms.Flush();
                    return ms;
                }


            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public static void InitializeWorkBook(string path)
        {
            try
            {
                FileStream excelFile = new FileStream(path, FileMode.Open, FileAccess.Read);
                hssfWorkBook = new HSSFWorkbook(excelFile);

                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "Acctrue";
                hssfWorkBook.DocumentSummaryInformation = dsi;

            }
            catch (Exception ex)
            {

                throw ex;
            }



        }
        public static MemoryStream GerneralExcelExport(DataTable dt, string path) {
            try
            {
                InitializeWorkBook(path);
                HSSFSheet sheet1 = hssfWorkBook.GetSheet("Sheet1");
                if (sheet1 == null)
                {
                    return null;
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        sheet1.CreateRow(i + 2).CreateCell(0).SetCellValue(i + 1);
                        sheet1.CreateRow(i + 2).CreateCell(j+1).SetCellValue(dt.Rows[i][j].ToString());
                    }
                 
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    hssfWorkBook.Write(ms);
                    ms.Flush();
                    return ms;
                }


            }
            catch (Exception ex)
            {

                throw ex;
            }
        
        }
    }
}
